﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Net;
using DBSchemaService.Utils;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Server;
using Microsoft.TeamFoundation.VersionControl.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Net.Mail;
using System.Threading;
using System.Text;
using System.Linq;
namespace DBSchemaService.Core
{
    public class WorkItemProcessor
    {        
        internal readonly string tfsServerURL = ConfigurationManager.AppSettings["tfsServerURL"];
        internal readonly string wiqlQuery = ConfigurationManager.AppSettings["wiqlQuery"];
        internal readonly string tfsUserName = ConfigurationManager.AppSettings["tfsUserName"];
        internal readonly string tfsPassword = ConfigurationManager.AppSettings["tfsPassword"];
        internal readonly string fileToCheckIn = ConfigurationManager.AppSettings["fileToCheckIn"];
        internal readonly string AppName = ConfigurationManager.AppSettings["AppName"];
        internal readonly List<SchemaConfiguration> schemaConfigurations = ConfigurationHelper.GetSchemaConfigurations();
        internal readonly int _schemaPollInterval = Convert.ToInt32(ConfigurationManager.AppSettings["schemaPollInterval"]);
        internal readonly string _emailMeWhenSuccessful = ConfigurationManager.AppSettings["groupEmail"];
        internal readonly string _emailMeWhenSuccessfulName = ConfigurationManager.AppSettings["groupName"];
        internal readonly string _workspace = ConfigurationManager.AppSettings["workspace"];
        internal readonly string _localPathStartDir = ConfigurationManager.AppSettings["localPathStartDir"];
        internal readonly bool _isStoredProcsProcessed = bool.Parse(ConfigurationManager.AppSettings["isStoredProcsProcessed"]);
        internal readonly bool _isDBCreateScriptEnabled = bool.Parse(ConfigurationManager.AppSettings["isDBCreateScriptEnabled"]);
        internal readonly bool _isFreshWorkspace = bool.Parse(ConfigurationManager.AppSettings["isFreshWorkspace"]);
        internal readonly string _branches = ConfigurationManager.AppSettings["branches"];
        internal readonly string _databases = ConfigurationManager.AppSettings["databases"];
        internal readonly bool _isSchemasProcessed = bool.Parse(ConfigurationManager.AppSettings["isSchemasProcessed"]);

        internal bool _stopService = false;
        internal bool _processing = false;

        internal string _schemaStructure = ConfigurationManager.AppSettings["schemaStructure"];
        internal string _schemaStructureStart = ConfigurationManager.AppSettings["schemaStructureStart"];
        internal string _schemaStructureEnd = ConfigurationManager.AppSettings["schemaStructureEnd"];
        internal string _updateSCHChanges = ConfigurationManager.AppSettings["updateSCHChanges"];


        public void StartProcessor()
        {
            Thread schemaWorker = new Thread(new ThreadStart(Start));
            schemaWorker.Start();
        }

        public void Start()
        {

            bool isScriptNew = false, isWorkspaceDeleted = false;
            string sqlToModifySchema = string.Empty, metadata = string.Empty, projectName = string.Empty, dateFormat = string.Empty, structureFilePath = string.Empty, serverPathRoot = string.Empty, localPathRoot = string.Empty, sqlToModifySchemaBranch = string.Empty, BranchSQL = string.Empty, scriptToAdd = string.Empty, db = string.Empty, branch = string.Empty, dbForSchemaChanges = string.Empty;
            string line = string.Empty, serverPath = string.Empty, schemasRootLocalPath = string.Empty, localPath = string.Empty, schemaChangeLocalPath = string.Empty, schemaChangeServerPath = string.Empty, BranchserverPath = string.Empty, BranchlocalPath = string.Empty, serverPathForHistoryFile = string.Empty, localPathForHistoryFile = string.Empty;
            double versionNumber = 0, result = 0;
            WorkItemCheckinInfo[] checkinInfo = new WorkItemCheckinInfo[1];
            NetworkCredential tfsCredential;
            TeamFoundationServer tfs;
            WorkItemStore wis;
            WorkItemCollection wic = null;
            VersionControlServer sourceControl;
            StoredProcedureProcessor sProcFileProcessor = new StoredProcedureProcessor();
            int changesetNumber = 0;
            StringBuilder structureFileBuilder;
            List<EmailInfo> email;
            string[] branchNames, databaseNames;


            try
            {
                if (string.IsNullOrEmpty(tfsServerURL) || string.IsNullOrEmpty(wiqlQuery))
                {
                    throw new ArgumentNullException();
                }
            }
            catch (ArgumentNullException ex)
            {
                Console.WriteLine(ex);
            }

            tfsCredential = new NetworkCredential(tfsUserName, tfsPassword);

            //TODO: replace with TfsTeamProjectCollection or TfsConfigurationServer               
            using (tfs = new TeamFoundationServer(tfsServerURL, tfsCredential))
            {
                try
                {
                    tfs.Authenticate();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
              
                while (!_stopService)
                {
                    isScriptNew = false;
                    _processing = true;
                    email = new List<EmailInfo>();
                    wis = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));
                    wic = wis.Query(wiqlQuery);

                    if (_isSchemasProcessed)
                    {
                        //Process each potential schema change
                        foreach (WorkItem wi in wic)
                        {
                            try
                            {
                                sqlToModifySchema = DBSchemaManager.Core.Shared.CleanString(wi.Fields["SQL"].Value.ToString().Trim());


                                //using (StreamWriter outfile = new StreamWriter(Path.Combine(Environment.CurrentDirectory, "sqltoappend.txt"), false))
                                //{
                                //    outfile.Write(sqlToModifySchema);
                                //}

                                using (FileStream outfile = new FileStream(Path.Combine(Environment.CurrentDirectory, "sqltoappend.txt"), FileMode.Create))
                                {
                                    using (StreamWriter sw = new StreamWriter(outfile, System.Text.ASCIIEncoding.ASCII))
                                    {
                                        sw.Write(DBSchemaManager.Core.Shared.CleanString(sqlToModifySchema));
                                    }
                                }

                                if (DatabaseHelper.ApplyDBChanges(Path.Combine(Environment.CurrentDirectory, "sqltoappend.txt"), wi))
                                {

                                    branch = wi.Fields["Branch"].Value.ToString(); //server path, local path changes based on branch                                                          
                                    db = wi.Fields["Database"].Value.ToString();

                                    if (IsDevBranch(branch.ToLower()))
                                        branch = "Dev";

                                    //get the local path and server path for the work item schema

                                    foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
                                    {
                                        if (db.ToLower() == schemaConfig.Database.ToLower())
                                        {
                                            serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "dev");
                                            schemasRootLocalPath = schemaConfig.SchemasRootLocalPath;
                                            localPathRoot = schemaConfig.LocalPathRoot;
                                            projectName = schemaConfig.Project;

                                            if (!IsDevBranch(branch.ToLower())) //localpath and serverpath are for the _structure files
                                            {

                                                if (branch.ToLower() == "dev-next")
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "dev-next");
                                                else if (branch.ToLower().Contains("dev") && (branch.Length > 3))
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "Development/" + branch);
                                                else
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "Releases/" + branch);

                                                schemasRootLocalPath = schemaConfig.SchemasRootLocalPath.Replace("Dev", branch);
                                                localPathRoot = schemaConfig.LocalPathRoot.Replace("Dev", branch);
                                            }

                                            localPathForHistoryFile = schemaConfig.LocalPathForHistoryFile;
                                            serverPathForHistoryFile = schemaConfig.TfsServerPathForHistoryFile;
                                            metadata = schemaConfig.Metadata;
                                            break;
                                        }
                                    }

                                    //update SCH.Changes
                                    UpdateChangesTableForTargetDB(metadata, db);

                                    sqlToModifySchema = DatabaseHelper.MakeSQLSafe(sqlToModifySchema);
                                    //sqlToModifySchema = DatabaseHelper.WrapSQLInExecStatement(sqlToModifySchema);                                
                                    versionNumber = DatabaseHelper.GetCurrentSchemaVersion(db, metadata, branch) + 1;

                                    //reset the schema structure!!
                                    _schemaStructure = ConfigurationManager.AppSettings["schemaStructure"];
                                    _schemaStructure = string.Format(_schemaStructure, versionNumber, wi.CreatedBy, wi.CreatedDate, wi.Id, GetTargetDB(db), versionNumber, GetDBTargetIndex(db), sqlToModifySchema);

                                    //create a workspace instance to modify schema.sql from tfs
                                    sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));

                                    Workspace[] workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());
                                    Workspace workspace = null;
                                    GetWorkspace(tfs, out sourceControl, out workspace);

                                    if (_isFreshWorkspace && !isWorkspaceDeleted)
                                    {
                                        //delete the local files from the localpathroot                                    
                                        //string[] filePaths = Directory.GetFiles(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));

                                        workspace.Delete();
                                        isWorkspaceDeleted = true;
                                        GetWorkspace(tfs, out sourceControl, out workspace);
                                    }

                                    GetStatus status = workspace.Get();
                                    Conflict[] conflicts = workspace.QueryConflicts(new string[] { }, false);
                                    foreach (Conflict conflict in conflicts)
                                    {
                                        conflict.Resolution = Resolution.OverwriteLocal;
                                        workspace.ResolveConflict(conflict);
                                    }

                                    //set up folder mapping before checkins
                                    if (!Directory.Exists(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db)))
                                        Directory.CreateDirectory(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));

                                    if (!workspace.IsLocalPathMapped(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db)))
                                    {
                                        if (branch.ToLower().Contains("dev") && (branch.Length > 3))
                                            workspace.Map(string.Format("$/California/{0}/{1}/DM4/Database/{2}/Schemas", projectName, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));
                                        else if (!double.TryParse(branch, out result)) //check if the branch is a number, which will be a release in the Releases Folder
                                            workspace.Map(string.Format("$/California/{0}/{1}/DM4/Database/{2}/Schemas", projectName, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));
                                        else
                                            workspace.Map(string.Format("$/California/{0}/Releases/{1}/{2}/Database/{3}/Schemas", projectName, branch, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));
                                    }
                                    dateFormat = "yyyyMMdd-HHmmss";
                                    schemaChangeLocalPath = Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db, string.Format("Schema-{0}-{1}.sql", DateTime.Now.ToString(dateFormat), wi.Id));

                                    //using (StreamWriter sw = File.CreateText(schemaChangeLocalPath))
                                    //{
                                    //    sw.Write(_schemaStructure);
                                    //}
                                    using (FileStream outfile = new FileStream(schemaChangeLocalPath, FileMode.Create))
                                    {
                                        using (StreamWriter sw = new StreamWriter(outfile, System.Text.ASCIIEncoding.ASCII))
                                        {
                                            sw.Write(DBSchemaManager.Core.Shared.CleanString(_schemaStructure));
                                        }
                                    }

                                    //add the file to TFS!!
                                    int num = workspace.PendAdd(schemaChangeLocalPath);

                                    //get pending change and check it in to TFS
                                    PendingChange[] changesForNewSchema = workspace.GetPendingChanges((new string[] { schemaChangeLocalPath }), RecursionType.Full);

                                    //check-in to TFS
                                    if (changesForNewSchema.Length > 0)
                                    {
                                        checkinInfo = new WorkItemCheckinInfo[] { new WorkItemCheckinInfo(wi, WorkItemCheckinAction.Associate) };
                                        changesetNumber = workspace.CheckIn(changesForNewSchema, "Modified by Schema Change Service. " + Environment.NewLine + " ***NO_CI***", null, checkinInfo, null);
                                        wi.Fields["Version"].Value = versionNumber.ToString();
                                        wi.State = "Closed";
                                        wi.Reason = "Completed";
                                        wi.Save();
                                        //workspace.SetLock(schemaChangeLocalPath, LockLevel.CheckOut);
                                        DatabaseHelper.UpdateSchemaVersion(versionNumber, db, branch, metadata);

                                        //notify developer by email that Schema Change succeeded. 
                                        Identity user = TFSHelper.GetTFSUser(tfs, wi);
                                        if (user != null)
                                            EmailHelper.NotifyDeveloper(user, wi, changesetNumber);

                                        if (changesetNumber > 0)
                                            isScriptNew = true;
                                    }
                                }
                                else
                                {
                                    if (wi.State != "Active")
                                    {
                                        wi.State = "Active";
                                        wi.Reason = "Failed";
                                        ArrayList test = wi.Validate();
                                        wi.Save();
                                        //notify developer by email that Schema Change failed. 
                                        Identity user = TFSHelper.GetTFSUser(tfs, wi);
                                        if (user != null)
                                            EmailHelper.NotifyDeveloper(user, wi, sqlToModifySchema);
                                    }
                                    else
                                    {
                                        wi.State = "Testing";
                                        wi.Reason = "Ready for Test";
                                        wi.Save();
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                LogErrorToWIAndFile(wi, ex);
                            }

                        }
                        try
                        {
                            //get workspace
                            Workspace workspace;

                            GetWorkspace(tfs, out sourceControl, out workspace);

                            //PROCESS ALL BRANCHES HERE
                            branchNames = _branches.Split(',');
                            databaseNames = _databases.Split(',');


                            foreach (string branchName in branchNames) //each branch get specific local path
                            {
                                foreach (string databaseName in databaseNames)
                                {
                                    foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
                                    {
                                        if (schemaConfig.Database.ToLower() == databaseName.ToLower())
                                        {
                                            serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "Dev");
                                            localPathRoot = schemaConfig.LocalPathRoot;
                                            projectName = schemaConfig.Project;

                                            if (branchName.ToLower() != "dev") //localpath and serverpath are for the _structure files
                                            {
                                                if (branchName.ToLower() == "dev-next")
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "Dev-Next");
                                                else if (branchName.ToLower() == "main")
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, "Main");
                                                else if (branchName.ToLower().Contains("dev") && (branchName.Length > 3))
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, string.Format("Development/{0}", branchName));
                                                else
                                                    serverPathRoot = string.Format(schemaConfig.ServerPathRoot, string.Format("Releases/{0}/{1}", branchName, branchName));

                                                localPathRoot = schemaConfig.LocalPathRoot.Replace("Dev", branchName);
                                            }
                                            break;
                                        }
                                    }

                                    GetWorkspaceForSchemas(tfs, out sourceControl, out workspace, branchName, databaseName, projectName);
                                    workspace.Get();

                                    localPath = Path.Combine(localPathRoot, string.Format("{0}_schemachanges.sql", databaseName));
                                    serverPath = Path.Combine(serverPathRoot, string.Format("{0}_schemachanges.sql", databaseName));

                                    if (_isFreshWorkspace && !isWorkspaceDeleted)
                                    {

                                        workspace.Delete();

                                        string[] filePaths = Directory.GetFiles(localPathRoot);
                                        foreach (string filePath in filePaths)
                                        {
                                            File.SetAttributes(filePath, FileAttributes.Normal);//Get rid of read-only
                                            File.Delete(filePath);
                                        }

                                        GetWorkspace(tfs, out sourceControl, out workspace);
                                    }

                                    //CREATE the *dbname*_schemachanges.sql file   
                                    if (!Directory.Exists(localPathRoot))
                                        Directory.CreateDirectory(localPathRoot);

                                    if (!workspace.IsLocalPathMapped(localPath))
                                        workspace.Map(serverPath, localPath);

                                    if (!workspace.IsLocalPathMapped(localPathRoot))
                                        workspace.Map(serverPathRoot, localPathRoot);

                                    workspace.Get();

                                    structureFileBuilder = new StringBuilder();

                                    //add the beginning try catch
                                    structureFileBuilder.AppendLine(_schemaStructureStart);

                                    DirectoryInfo dir = new DirectoryInfo(Path.Combine(_localPathStartDir, Environment.MachineName, branchName, "Database", databaseName));
                                    if (dir.Exists)
                                        ProcessDirectoryTree(structureFileBuilder, ref line, dir);

                                    if (structureFileBuilder.ToString() == _schemaStructureStart) //this means there are no schema changes yet
                                    {
                                        structureFileBuilder.AppendLine("PRINT ''");
                                    }

                                    //additional file formatting (add the try catch)
                                    structureFileBuilder.AppendLine(_schemaStructureEnd);

                                    //create file if it doesn't exist, then check in.
                                    if (!File.Exists(localPath))
                                    {
                                        using (StreamWriter sw = File.CreateText(localPath))
                                        {
                                        }
                                        int numChangesStructure = workspace.PendAdd(localPath);
                                        PendingChange[] addingNewStructureFile = workspace.GetPendingChanges((new string[] { localPath }));
                                        if (addingNewStructureFile.Length > 0)
                                            changesetNumber = workspace.CheckIn(addingNewStructureFile, "Modified by Schema Change Service. " + Environment.NewLine + " ***NO_CI***", null, null, null);
                                    }

                                    int numFilesCheckedOut = workspace.PendEdit(localPath);
                                    workspace.SetLock(localPath, LockLevel.CheckOut);
                                    using (StreamWriter sw = new StreamWriter(localPath, false))
                                    {
                                        sw.Write(structureFileBuilder.ToString());
                                    }

                                    PendingChange[] changesForNewStructureFile = workspace.GetPendingChanges((new string[] { localPath }), RecursionType.Full);
                                    if (changesForNewStructureFile.Length > 0)
                                    {
                                        changesetNumber = workspace.CheckIn(changesForNewStructureFile, "Modified by Schema Change Service. " + Environment.NewLine + " ***NO_CI***", null, null, null);

                                        if (changesetNumber > 0)
                                            email.Add(new EmailInfo(_emailMeWhenSuccessful, _emailMeWhenSuccessfulName, databaseName, serverPath, changesetNumber));
                                    }
                                    workspace.SetLock(localPath, LockLevel.CheckOut);
                                }
                            }
                            if (_isFreshWorkspace)
                                isWorkspaceDeleted = true;
                        }

                        catch (Exception ex)
                        {
                            using (StreamWriter sw = new StreamWriter(@"C:\Logs\log_errors_em.txt", true))
                            {
                                sw.WriteLine(ex.Message);
                                sw.WriteLine(ex.Source);
                                sw.WriteLine(ex.InnerException);
                                sw.WriteLine(ex.StackTrace);
                            }
                        }



                    }
                    //Create The Stored Procedure File Here
                    try
                    {
                        if (_isStoredProcsProcessed)
                            isScriptNew = sProcFileProcessor.Start(tfs, false, email, isScriptNew);
                    }
                    catch (Exception ex)
                    {
                        using (StreamWriter sw = new StreamWriter(@"C:\Logs\log_errors_em.txt", true))
                        {
                            sw.WriteLine(ex.Message);
                            sw.WriteLine(ex.Source);
                            sw.WriteLine(ex.InnerException);
                            sw.WriteLine(ex.StackTrace);
                        }
                    }

                    //Update the Create Script File here
                    try
                    {
                        if (isScriptNew && _isDBCreateScriptEnabled)
                        {
                            branchNames = _branches.Split(',');
                            foreach (string branchName in branchNames)
                            {
                                if (branchName.ToLower() == "dev")
                                    DatabaseHelper.GenerateDatabaseScript(tfs);
                                else
                                    DatabaseHelper.GenerateDatabaseScript(tfs, branchName);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        using (StreamWriter sw = new StreamWriter(@"C:\Logs\log_errors_em.txt", true))
                        {
                            sw.WriteLine(ex.Message);
                            sw.WriteLine(ex.Source);
                            sw.WriteLine(ex.InnerException);
                            sw.WriteLine(ex.StackTrace);
                        }
                    }

                    System.Threading.Thread.Sleep(_schemaPollInterval);
                }
            }
            _processing = false;
        }

        private void UpdateChangesTableForTargetDB(string metadata, string db)
        {
            try
            {
                _updateSCHChanges = ConfigurationManager.AppSettings["updateSCHChanges"];
                _updateSCHChanges = string.Format(_updateSCHChanges, metadata, metadata, metadata);
                DatabaseHelper.UpdateSchChangesTable(_updateSCHChanges, db);
            }
            catch (Exception ex)
            {
                using (StreamWriter sw = new StreamWriter(@"C:\Logs\log_errors_em.txt", true))
                {
                    sw.WriteLine(ex.Message);
                    sw.WriteLine(ex.Source);
                    sw.WriteLine(ex.InnerException);
                    sw.WriteLine(ex.StackTrace);
                }
            }
        }

        private static void LogErrorToWIAndFile(WorkItem wi, Exception ex)
        {
            wi.State = "Active";
            wi.Reason = "Failed";

            //wi.Fields["Info"].Value += String.Format("<p>[{0}] ERROR: {1}</p>", DateTime.Now.ToString(), ex.Message);
            wi.History += String.Format("<p>[{0}] ERROR: {1}</p>", DateTime.Now.ToString(), ex.Message);
            try
            {
                wi.Save();
            }
            catch (Exception ex2)
            {
                using (StreamWriter sw = new StreamWriter(@"C:\Logs\log_errors_em.txt", true))
                {
                    sw.WriteLine(ex2.Message);
                    sw.WriteLine(ex2.Source);
                    sw.WriteLine(ex2.InnerException);
                    sw.WriteLine(ex2.StackTrace);
                }
            }
            using (StreamWriter sw = new StreamWriter(@"C:\Logs\log_errors_em.txt", true))
            {
                sw.WriteLine(ex.Message);
                sw.WriteLine(ex.Source);
                sw.WriteLine(ex.InnerException);
                sw.WriteLine(ex.StackTrace);
            }
        }

        /// <summary>
        /// This is needed because sprocs that run schema changes on local dev computer runs client db stuff on almain
        /// </summary>
        /// <param name="databaseSource"></param>
        /// <returns></returns>
        private string GetTargetDB(string databaseSource)
        {

            switch (databaseSource.ToLower())
            {
                case "clientdb":
                    return "main";

                default:
                    return databaseSource;
            }
        }


        private void GetWorkspace(TeamFoundationServer tfs, out VersionControlServer sourceControl, out Workspace workspace)
        {
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            Workspace[] workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());
            workspace = null;
            if (workspaces.Length == 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
            {
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();
            }
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
            {
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            }
        }

        private void GetWorkspaceForSchemas(TeamFoundationServer tfs, out VersionControlServer sourceControl, out Workspace workspace, string branch, string db, string projectName)
        {
            double result = 0;
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            Workspace[] workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());
            workspace = null;
            if (workspaces.Length == 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
            {
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();
            }
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
            {
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            }

            //set up folder mapping before checkins
            if (!Directory.Exists(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db)))
                Directory.CreateDirectory(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));

            if (!workspace.IsLocalPathMapped(Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db)))
            {

                if (branch.ToLower().Contains("dev") && (branch.Length > 3))
                    workspace.Map(string.Format("$/California/{0}/Development/{1}/Database/{2}/Schemas", projectName, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));
                else if (!double.TryParse(branch, out result)) //check if the branch is a number, which will be a release in the Releases Folder
                    workspace.Map(string.Format("$/California/{0}/{1}/DM4/Database/{2}/Schemas", projectName, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));
                else
                    workspace.Map(string.Format("$/California/{0}/Releases/{1}/{2}/Database/{3}/Schemas", projectName, branch, branch, db), Path.Combine(_localPathStartDir, Environment.MachineName, branch, "Database", db));

            }
        }



        private bool IsDevBranch(string branch)
        {
            //verify if branch is selected from dropdown on the work item
            if (branch == string.Empty || branch == "dev")
                return true;

            return false;
        }


        private bool IsBranchSelected(WorkItem IWJ)
        {
            //verify if branch is selected from dropdown on the work item
            string Branch = string.Empty;
            Branch = IWJ.Fields["Branch"].Value.ToString().Trim().Normalize();
            if (Branch == string.Empty)
                return false;

            return true;
        }

        //private string SchemaScriptToAppend(int versionNumber, string sqlToModifySchema, string createdBy, string database)
        //{

        //    string selectVersion = string.Empty;
        //    string updateSchemaVersion = string.Empty;

        //    foreach (SchemaConfiguration schemaConfig in schemaConfigurations)
        //    {
        //        if (database.ToLower() == schemaConfig.Database.ToLower())
        //        {
        //            updateSchemaVersion = schemaConfig.UpdateVersionQuery;

        //            updateSchemaVersion = updateSchemaVersion.Replace("@", (versionNumber + 1).ToString());
        //            selectVersion = schemaConfig.SelectVersionQuery;
        //            break;
        //        }

        //    }

        //    string conditionStart = "BEGIN";
        //    string condition = string.Format("IF ({0}) = {1}", selectVersion, versionNumber);
        //    string conditionEnd = "END";
        //    string conditionGo = "GO";
        //    string setNoCount = "SET NOCOUNT OFF";
        //    string comments = string.Format("--Version {0}: [ Created by {1} on {2} {3} ]", versionNumber, createdBy, DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
        //    return comments + Environment.NewLine + condition +
        //                                 Environment.NewLine + conditionStart + Environment.NewLine + sqlToModifySchema + Environment.NewLine
        //                                 + updateSchemaVersion + Environment.NewLine + conditionEnd + Environment.NewLine + conditionGo + Environment.NewLine + setNoCount;

        //}

        private int GetDBTargetIndex(string db)
        {
            switch (db.ToLower())
            {
                case "store":
                    return 3;
                case "clientdb":
                    return 1;
                case "main":
                    return 2;
                case "auth":
                    return 4;
                default:
                    return 0;
            }
        }

        private static void ProcessDirectoryTree(StringBuilder structureFileBuilder, ref string line, DirectoryInfo root)
        {
            List<DirectoryInfo> subDirs = null;
            List<FileInfo> filesToProcess = null;
            StringBuilder singleScriptBuilder = new StringBuilder();
            filesToProcess = root.GetFiles().Where(a => a.Extension.Equals(".sql") && a.FullName.Contains("Schema")).OrderBy(a => a.CreationTime).ToList();

            foreach (FileInfo f in filesToProcess)
            {
                //Add to the stringbuilder for checking in
                using (StreamReader reader = new StreamReader(f.FullName))
                {
                    while ((line = reader.ReadLine()) != null)
                    {
                        structureFileBuilder.AppendLine(DBSchemaManager.Core.Shared.CleanString(line));
                    }
                }
            }

            // Now find all the subdirectories under this directory.
            subDirs = root.GetDirectories().ToList();

            foreach (System.IO.DirectoryInfo dirInfo in subDirs)
            {
                // Resursive call for each subdirectory.
                ProcessDirectoryTree(structureFileBuilder, ref line, dirInfo);
            }
        }


        public static List<BranchObject> GetSolutionBranch(string serverName, string teamProjectName, string projectName, string branch, TeamFoundationServer tfs)
        {
            var branches = new List<BranchObject>();
            var versionControl = tfs.GetService<VersionControlServer>();
            var allBranches = versionControl.QueryRootBranchObjects(RecursionType.Full);

            foreach (var branchObject in allBranches)
            {
                if (branchObject.Properties.RootItem.Item.ToUpper().Contains(teamProjectName.ToUpper()) &&
                        branchObject.Properties.RootItem.Item.ToUpper().Contains(projectName.ToUpper()) &&
                        branchObject.Properties.RootItem.Item.ToUpper().Contains(branch.ToUpper())
                    )
                {
                    branches.Add(branchObject);
                }
            }
            return branches;
        }

        private string GetTFSDatabaseFolder(string tfsPath)
        {
            return string.Concat(tfsPath, "-Package/Database");
        }

        public void Stop()
        {
            _stopService = true;
            while (_processing)
            {
                System.Threading.Thread.Sleep(1000);
            }
        }
    }
}

